{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "from tqdm import tqdm\n",
    "\n",
    "folder = \"/media/jie/新加卷/pku_data/3571万专利申请全量数据1985-2022年\"\n",
    "file = \"/media/jie/新加卷/pku_data/3571万专利申请全量数据1985-2022年/福建省.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys\n",
    "sys.path.append(\"/home/jie/.key\")\n",
    "# 本地密码存储文件\n",
    "from sql_key import database, password"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\\nif __name__ == \"__main__\":\\n    folder = \"/home/jie/Desktop/industry_info\"\\n    print(f\"文件总数: {len(os.listdir(folder))}\")\\n    cnt = 0\\n    for file_name in os.listdir(folder):\\n        if file_name.endswith(\".csv\"):\\n            cnt += 1\\n            filename = os.path.join(folder, file_name)\\n            print(cnt, file_name)\\n            insert_sql_by_csv(filename)\\n\\n    # mysql -h 127.0.0.1 -P 3306 -u root -p\\n    # nohup python xxx.py > xxx.log 2>&1 &\\n'"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import os\n",
    "from tqdm import tqdm\n",
    "import pandas as pd\n",
    "import pymysql\n",
    "\n",
    "\n",
    "column_mapping = {\n",
    "    '专利公开号': 'publication_number',\n",
    "    '专利名称': 'patent_name',\n",
    "    '专利类型': 'patent_type',\n",
    "    '专利摘要': 'abstract',\n",
    "    '申请人': 'applicant',\n",
    "    '专利申请号': 'application_number',\n",
    "    '申请日': 'application_date',\n",
    "    '申请公布日': 'publication_date',\n",
    "    '授权公布号': 'grant_publication_number',\n",
    "    '授权公布日': 'grant_publication_date',\n",
    "    '申请地址': 'application_address',\n",
    "    '主权项': 'claims',\n",
    "    '发明人': 'inventor',\n",
    "    '分类号': 'classification_number',\n",
    "    '主分类号': 'main_classification_number',\n",
    "    '代理机构': 'agency',\n",
    "    '分案原申请号': 'original_application_number',\n",
    "    '优先权': 'priority',\n",
    "    '国际申请': 'international_application',\n",
    "    '国际公布': 'international_publication',\n",
    "    '代理人': 'agent',\n",
    "    '省份或国家代码': 'province_or_country_code',\n",
    "    '法律状态': 'legal_status',\n",
    "    '专利领域': 'patent_field',\n",
    "    '专利学科': 'patent_subject',\n",
    "    '多次公布': 'multiple_publications'\n",
    "}\n",
    "\n",
    "\n",
    "def trans2int(item):\n",
    "    if pd.isna(item):\n",
    "        return None\n",
    "    try:\n",
    "        return int(eval(item))\n",
    "    except:\n",
    "        return None\n",
    "\n",
    "\n",
    "def parse_item(row, attr_name, args: list):\n",
    "    \n",
    "    assert isinstance(args, list)\n",
    "\n",
    "    # 字符截断\n",
    "    trunc_item = {\n",
    "        \"applicant\" : 255,\n",
    "        \"application_address\": 255,\n",
    "        \"inventor\": 1024,\n",
    "        \"classification_number\": 1024,\n",
    "        \"main_classification_number\": 127,\n",
    "        \"agency\" : 255,\n",
    "        \"original_application_number\": 127,\n",
    "        \"priority\" : 511,\n",
    "        \"international_application\": 255,\n",
    "        \"international_publication\": 255,\n",
    "        \"agent\": 127,\n",
    "        \"patent_field\" : 255,\n",
    "        \"patent_subject\" : 255,\n",
    "        \"multiple_publications\": 63,\n",
    "    }\n",
    "\n",
    "    ans = []\n",
    "    for attr in attr_name:\n",
    "        en_attr = column_mapping[attr]\n",
    "        item = row.get(attr, None)\n",
    "        \n",
    "        if pd.isna(item):\n",
    "            ans.append(None)\n",
    "            continue\n",
    "        \n",
    "        if en_attr == \"province_or_country_code\":\n",
    "            item = trans2int(item)\n",
    "        # 异常字符捕获\n",
    "        elif isinstance(item, str):\n",
    "            item = item.strip()\n",
    "            # 有 空串 和 -\n",
    "            if len(item) in [0, 1]:\n",
    "                item = None\n",
    "        \n",
    "        if attr in trunc_item.keys():\n",
    "            if isinstance(item, str):\n",
    "                max_len = trunc_item[attr]\n",
    "                item = item[:max_len]\n",
    "\n",
    "        ans.append(item)\n",
    "    ans += args\n",
    "    \n",
    "    return tuple(ans)\n",
    "\n",
    "\n",
    "def insert_sql_by_csv(file):\n",
    "    df = pd.read_csv(file, nrows=10, low_memory=False)\n",
    "    \n",
    "    province = os.path.basename(file).split('.')[0]\n",
    "    # 连接到MySQL数据库\n",
    "    connection = pymysql.connect(\n",
    "        host=\"localhost\",  # MySQL数据库的主机\n",
    "        user=\"root\",  # MySQL用户名\n",
    "        password=password,  # MySQL密码\n",
    "        database=database,  # 你要插入数据的数据库\n",
    "        charset=\"utf8mb4\",\n",
    "        cursorclass=pymysql.cursors.DictCursor,\n",
    "    )\n",
    "    \n",
    "    BATCH_SIZE = 1000\n",
    "\n",
    "    tmp_list = list(column_mapping.values()) + [\"province\"]\n",
    "    sql = f\"\"\"\n",
    "            INSERT INTO patents (\n",
    "                {\", \".join(tmp_list)}\n",
    "            ) VALUES (\n",
    "                {', '.join(['%s'] * len(tmp_list))}\n",
    "            );\n",
    "            \"\"\".strip()\n",
    "    \n",
    "    # 插入数据到MySQL\n",
    "    try:\n",
    "        with connection.cursor() as cursor:\n",
    "            batch = []\n",
    "            for _, row in tqdm(df.iterrows(), total=len(df)):\n",
    "    \n",
    "                batch.append(\n",
    "                        parse_item(row, list(column_mapping.keys()), [province])\n",
    "                    )\n",
    "                                \n",
    "                # 当批次达到 BATCH_SIZE 时执行批量插入\n",
    "                if len(batch) >= BATCH_SIZE:\n",
    "                    cursor.executemany(sql, batch)\n",
    "                    batch = []  # 清空批次\n",
    "                    \n",
    "            # 插入剩余的未满批次的数据\n",
    "            if batch:\n",
    "                cursor.executemany(sql, batch)\n",
    "\n",
    "            # 提交事务\n",
    "            connection.commit()\n",
    "            \n",
    "    except Exception as e:\n",
    "        print(f\"插入数据时出现错误: {e}\")\n",
    "        connection.rollback()\n",
    "\n",
    "    finally:\n",
    "        connection.close()\n",
    "\n",
    "\n",
    "\n",
    "\"\"\"\n",
    "if __name__ == \"__main__\":\n",
    "    folder = \"/home/jie/Desktop/industry_info\"\n",
    "    print(f\"文件总数: {len(os.listdir(folder))}\")\n",
    "    cnt = 0\n",
    "    for file_name in os.listdir(folder):\n",
    "        if file_name.endswith(\".csv\"):\n",
    "            cnt += 1\n",
    "            filename = os.path.join(folder, file_name)\n",
    "            print(cnt, file_name)\n",
    "            insert_sql_by_csv(filename)\n",
    "\n",
    "    # mysql -h 127.0.0.1 -P 3306 -u root -p\n",
    "    # nohup python xxx.py > xxx.log 2>&1 &\n",
    "\"\"\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 10/10 [00:00<00:00, 15125.51it/s]\n"
     ]
    }
   ],
   "source": [
    "insert_sql_by_csv(file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
